using System;
using System.Collections.Generic;
using System.Text;
using FluorineFx;
using FluorineFx.AMF3;

using System.IO;
using System.Net;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.Web;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
namespace HouseDiyService
{
    [RemotingService("Fluorine sample service")]
    class HouseDiyAdmin
    {
        private HouseDiyAction housediy = new HouseDiyAction();

        public List<Brand> addABrand(Brand brand)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Brand", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Brand");
                DataRow dr = dataSet.Tables[0].NewRow();

                dr["brandName"] = brand.brandName;
                //dr["pictureSrc"] = brand.pictureSrc;
                dr["brandDesc"] = brand.brandDesc;
                dr["pictureID"] = brand.pictureID;
                dr["brandCreateDate"] = brand.brandCreateDate;
                dr["brandCreator"] = brand.brandCreator;
                dr["brandEnglishName"] = brand.brandEnglishName;
                dr["brandOrder"] = brand.brandOrder;

                DataTable dt = dataSet.Tables[0];
                dt.Rows.Add(dr);
                adapter.Update(dataSet, "Brand");
            }
            return housediy.getAllBrands();
        }

        public List<TempCantavil> addACantavil(Cantavil cantavil)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Cantavil", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Cantavil");
                DataRow dr = dataSet.Tables[0].NewRow();

                dr["cantavilAddress"] = cantavil.cantavilAddress;
                dr["cantavilCreateDate"] = cantavil.cantavilCreateDate;
                dr["cantavilCreator"] = cantavil.cantavilCreator;
                dr["cantavilDeveloper"] = cantavil.cantavilDeveloper;
                dr["cantavilName"] = cantavil.cantavilName;
                dr["cantavilPic"] = cantavil.cantavilPic;
                dr["cantavilRemark"] = cantavil.cantavilRemark;
                dr["cantavilType"] = cantavil.cantavilType;
                dr["developDesc"] = cantavil.developDesc;
                dr["typeID"] = cantavil.typeID;
                dr["pictureID"] = cantavil.pictureID;

                DataTable dt = dataSet.Tables[0];
                dt.Rows.Add(dr);
                adapter.Update(dataSet, "Cantavil");
            }
            return housediy.getCantavils();
        }

        public ArrayCollection addAGoods(Goods goods)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Goods", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Goods");
                DataRow dr = dataSet.Tables[0].NewRow();

                dr["brandID"] = goods.brandID;
                dr["typeID"] = goods.typeID;
                dr["pictureIDs"] = goods.pictureIDs;
                dr["goodsName"] = goods.goodsName;
                dr["goodsType"] = goods.goodsType;
                dr["goodsLocation"] = goods.goodsLocation;
                dr["packageCount"] = goods.packageCount;
                dr["goodsPackage"] = goods.goodsPackage;
                dr["goodsMaterial"] = goods.goodsMaterial;
                dr["goodsStyle"] = goods.goodsStyle;
                dr["goodsBill"] = goods.goodsBill;
                dr["goodsGuaranteePeriod"] = goods.goodsGuaranteePeriod;
                dr["goodsCityService"] = goods.goodsCityService;
                dr["goodsUnit"] = goods.goodsUnit;
                dr["goodsInstallCost"] = goods.goodsInstallCost;
                dr["goodsCreator"] = goods.goodsCreator;
                dr["goodsCreateDate"] = goods.goodsCreateDate;

                DataTable dt = dataSet.Tables[0];
                dt.Rows.Add(dr);
                adapter.Update(dataSet, "Goods");
            }

            Int64 goodsID = _findLatestId("Goods");
            ArrayCollection ar = new ArrayCollection();
            // addPictures(goods.pictures, goodsID);
            ar.Add(addColors(goods.colors, goodsID));
            ar.Add(addMolde(goods.models, goodsID));
            return ar;
        }

        public List<string> getCantavilType()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string select = "select * from Cantavil" ;
            DataSet ds = sqlhelper.FillDataSet(select, null);

            List<string> cantavilTypes = new List<string>();

            DataRowCollection rows = ds.Tables[0].Rows;
            for (int m = 0; m < rows.Count; m++)
            {
                Cantavil cantavil = new Cantavil();
                cantavilTypes.Add(rows[m]["cantavilType"].ToString());
            }
            return cantavilTypes;
        }

        public List<XmlDocument> getGoodsType()
        {
            List<XmlDocument> goodsType = new List<XmlDocument>();
            //XmlDocument xmldoc1 = _getDoorXML();
            goodsType.Add(_getDoor());
            //goodsType.Add(_getWindowXML());
            //goodsType.Add(_getFloorXML());
            //goodsType.Add(_getFurnitureXML());
            return goodsType;
        }

        public XmlDocument _getDoor()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where dictID= 1";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataTable db = ds.Tables[0];

            XmlDocument xmldoc = new XmlDocument();
            XmlDeclaration xmldecl;
            xmldecl = xmldoc.CreateXmlDeclaration("1.0", "gb2312", null);
            xmldoc.AppendChild(xmldecl);

            XmlElement ele = xmldoc.CreateElement("", "door", "");
            xmldoc.AppendChild(ele);
            XmlNode node1 = xmldoc.SelectSingleNode("door");
            for (int i = 0; i < db.Rows.Count; i++)
            {
                //XmlNode xmlnode = goodsTypeXml.CreateNode(XmlNodeType.Text, "type","");
                XmlElement ele1 = xmldoc.CreateElement("type");
                ele1.SetAttribute("typeName", db.Rows[i]["typeName"].ToString());
                ele1.SetAttribute("typeID", db.Rows[i]["typeID"].ToString());

                node1.AppendChild(ele1);
            }
            return xmldoc;
        }

      

        public ArrayCollection addPictures(List<Picture> pictures, Int64 goodsID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Picture", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Picture");
                DataTable dt = dataSet.Tables[0];

                ArrayCollection pics = new ArrayCollection();
                foreach (Picture picture in pictures)
                {
                    DataRow dr = dt.NewRow();
                    dr["goodsID"] = goodsID;
                    dr["picCreateDate"] = picture.picCreateDate;
                    dr["picCreator"] = picture.picCreator;
                    dr["pictureSrc"] = picture.pictureSrc;
                    dr["pictureType"] = picture.pictureType;
                    dr["typeID"] = picture.typeID;
                    dt.Rows.Add(dr);

                    adapter.Update(dataSet, "Picture");

                    pics.Add(getPicturesByID(_findLatestId("Picture")));
                }

                //adapter.Update(dataSet, "Picture");
                return pics;
            }
            
        }

        public void addGoodsRelated(ArrayCollection relateds)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from GoodsRelated", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "GoodsRelated");
                DataTable dt = dataSet.Tables[0];

                ArrayCollection pics = new ArrayCollection();
                foreach (GoodsRelated related in relateds)
                {
                    DataRow dr = dt.NewRow();
                    dr["colorID"] = related.colorID;
                    dr["goodsCount"] = related.goodsCount;
                    dr["goodsPrice"] = related.goodsPrice;
                    dr["goodsRelatedCreateDate"] = related.goodsRelatedCreateDate;
                    dr["goodsRelatedCreator"] = related.goodsRelatedCreator;
                    dr["modelID"] = related.modelID;
                    dr["pictureID"] = related.pictureID;                    

                    dt.Rows.Add(dr);

                    adapter.Update(dataSet, "GoodsRelated");                   
                }               
            }
        }
        public Picture getPicturesByID(Int64 pictureID)
        {
            HouseDiyHelper picsqlhelper = HouseDiyHelper.GetInstance();
            string picsql = "select * from Picture where pictureID = " + pictureID.ToString();
            DataSet picds = picsqlhelper.FillDataSet(picsql, null);
            DataRowCollection picrows = picds.Tables[0].Rows;

            /*ArrayCollection list = new ArrayCollection();
            for (int j = 0; j < picrows.Count; j++)
            {*/
                Picture pic = new Picture();
                pic.picCreator = picrows[0]["picCreator"].ToString();
                pic.pictureID = Convert.ToInt64(picrows[0]["pictureID"]);
                pic.pictureSrc = picrows[0]["pictureSrc"].ToString();
                pic.pictureType = picrows[0]["pictureType"].ToString();
                pic.typeID = Convert.ToInt64(picrows[0]["typeID"]);

               /* list.Add(pic);
            }*/

            return pic;
        }

        public ArrayCollection getAllPictures()
        {
            HouseDiyHelper picsqlhelper = HouseDiyHelper.GetInstance();
            string picsql = "select * from Picture ";
            DataSet picds = picsqlhelper.FillDataSet(picsql, null);
            DataRowCollection picrows = picds.Tables[0].Rows;

            ArrayCollection list = new ArrayCollection();
            for (int j = 0; j < picrows.Count; j++)
            {
                Picture pic = new Picture();
                pic.picCreator = picrows[j]["picCreator"].ToString();
                pic.pictureID =  Convert.ToInt64(picrows[j]["pictureID"]);
                pic.pictureSrc = picrows[j]["pictureSrc"].ToString();
                pic.pictureType = picrows[j]["pictureType"].ToString();
                pic.typeID = Convert.ToInt64(picrows[j]["typeID"]);
                list.Add(pic);
            }

            return list;
        }
        public List<Color> addColors(List<Color> colors, Int64 goodsID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Color", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Color");
                DataTable dt = dataSet.Tables[0];

                foreach (Color color in colors)
                {
                    DataRow dr = dt.NewRow();
                    dr["goodsID"] = goodsID;
                    dr["colorCreateDate"] = color.colorCreateDate;
                    dr["colorCreator"] = color.colorCreator;
                    dr["colorName"] = color.colorName;
                    dr["colorNum"] = color.colorNum;
                    dr["isDefault"] = color.isDefault;
                    dt.Rows.Add(dr);
                }

                adapter.Update(dataSet, "Color");

            }
            return housediy._getColorsByGoodsID(goodsID);
        }

        public List<Model> addMolde(List<Model> models, Int64 goodsID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Model", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Model");
                DataTable dt = dataSet.Tables[0];

                foreach (Model model in models)
                {
                    DataRow dr = dt.NewRow();
                    dr["goodsID"] = goodsID;
                    dr["height"] = model.height;
                    dr["isDefault"] = model.isDefault;
                    dr["length"] = model.length;
                    dr["modelCreateDate"] = model.modelCreateDate;
                    dr["modelCreator"] = model.modelCreator;
                    dr["modelSrc"] = model.modelSrc;
                    dr["packageSize"] = model.packageSize;
                    dr["packageWeight"] = model.packageWeight;
                    dr["width"] = model.width;
                    dt.Rows.Add(dr);
                }

                adapter.Update(dataSet, "Model");
            }

            return housediy._getModelsByGoodsID(goodsID);
        }

        public ArrayCollection addType(Type type)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand("select * from Type", connection);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                connection.Open();
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "Type");
                //DataTable dt = dataSet.Tables[0];

                DataRow dr = dataSet.Tables[0].NewRow();

                dr["parentID"] = type.parentID;
                dr["typeCreateDate"] = type.typeCreateDate;
                dr["typeCreator"] = type.typeCreator;
                dr["typeLevel"] = type.typeLevel;
                dr["typeName"] = type.typeName;
                dr["tag"] = type.tag;

                DataTable dt = dataSet.Tables[0];
                dt.Rows.Add(dr);
                adapter.Update(dataSet, "Type");
            }

            return getType();
        }

        public Int64 _findLatestId(String param1)
        {
            string sql = "SELECT   IDENT_CURRENT('" + param1 + "')";
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            return Convert.ToInt32(sqlhelper.ExecuteScalarDB(sql, null));

        }

        public ArrayCollection getType()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where parentID = 0 and typeLevel = 1";

            DataSet ds = sqlhelper.FillDataSet(sql, null);

            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection types = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Type newtype = new Type();
                newtype.typeName = rows[i]["typeName"].ToString();
                newtype.tag = rows[i]["tag"].ToString();
                newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
                newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
                newtype.typeCreator = rows[i]["typeCreator"].ToString();
                //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
                newtype.parentID = 0;
                newtype.children = _getChildren(newtype.typeID);

                types.Add(newtype); 
            }
            return types;
        }

        /* public ArrayCollection getType()
         {
             HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
             string sql = "select * from Type";
             DataSet ds = sqlhelper.FillDataSet(sql, null);
             DataRowCollection rows = ds.Tables[0].Rows;

             ArrayCollection types = new ArrayCollection();
             types.Add(new ArrayCollection());
             types.
             types.Add(getPicType);
             return types;

         }*/

        public ArrayCollection getPicType()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where typeFlag = 2";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection picTypes = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Type newtype = new Type();
                newtype.typeName = rows[i]["typeName"].ToString();
                newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
                newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
                newtype.typeCreator = rows[i]["typeCreator"].ToString();
                //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
                newtype.parentID = Convert.ToInt16(rows[i]["typeFlag"]);
                picTypes.Add(newtype);               
            }

            return picTypes;
        }

        public ArrayCollection getCityType()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where typeFlag = 1 and typeLevel = 1";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection picTypes = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Type newtype = new Type();
                newtype.typeName = rows[i]["typeName"].ToString();
                newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
                newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
                newtype.typeCreator = rows[i]["typeCreator"].ToString();
                //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
                newtype.parentID = Convert.ToInt16(rows[i]["typeFlag"]);
                newtype.children = _getChildren(newtype.typeID);

                picTypes.Add(newtype);
            }
            return picTypes;
        }

        public ArrayCollection _getChildren(Int64 typeID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where parentID= " + typeID.ToString();
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection children = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Type newtype = new Type();
                newtype.typeName = rows[i]["typeName"].ToString();
                newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
                newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
                newtype.typeCreator = rows[i]["typeCreator"].ToString();
                //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
                newtype.parentID = Convert.ToInt16(rows[i]["parentID"]);
                newtype.tag = rows[i]["tag"].ToString();
                newtype.children = _getChildren(newtype.typeID);

                children.Add(newtype); 
            }
            return children;
        }

        public ArrayCollection getDict()
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Dictionary ";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection dicts = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Dictionary dict = new Dictionary();
                dict.dictID = Convert.ToInt64(rows[i]["dictID"]);
                dict.dictName = rows[i]["dictName"].ToString();
                if(_getType(dict.dictID).Count >= 0)
                {
                    dict.types = _getType(dict.dictID);
                }
                else
                {
                   dict.types = null;
                }

                dicts.Add(dict);
            }
            return dicts;
        }

        public ArrayCollection _getType(Int64 dictID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Type where typeLevel = 1 and  dictID= " + dictID.ToString();
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection children = new ArrayCollection();
             

            for (int i = 0; i < rows.Count; i++)
            {
                Type newtype = new Type();
                newtype.typeName = rows[i]["typeName"].ToString();
                newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
                newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
                newtype.typeCreator = rows[i]["typeCreator"].ToString();
                //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
                newtype.parentID = Convert.ToInt16(rows[i]["parentID"]);
                if (_getChildren(newtype.typeID).Count <= 0)
                {
                    newtype.children = null;
                }
                else
                {
                    newtype.children = _getChildren(newtype.typeID);
                }
               

                children.Add(newtype);
            }
            return children;
        }



        public ArrayCollection getCantavilByPage(int pageIndex, int itemCount)
        {
           // HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string select = "select * from Cantavil";
           // DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataTable table = housediy._getTable(select, pageIndex, itemCount, "cantavilID", "asc");
            DataRowCollection rows = table.Rows;

            ArrayCollection list = new ArrayCollection();
            for (int i = 0; i < rows.Count; i++)
            {
                Cantavil cantavil = new Cantavil();
                cantavil.cantavilID = Convert.ToInt64(rows[i]["cantavilID"]);
                cantavil.typeID = Convert.ToInt64(rows[i]["typeID"]); 
                cantavil.cantavilName = rows[i]["cantavilName"].ToString();
                cantavil.cantavilType = rows[i]["cantavilType"].ToString();
                cantavil.cantavilAddress = rows[i]["cantavilAddress"].ToString();
                cantavil.cantavilRemark = rows[i]["cantavilRemark"].ToString();
                cantavil.cantavilPic = rows[i]["cantavilPic"].ToString();
                cantavil.cantavilDeveloper = rows[i]["cantavilDeveloper"].ToString();
                cantavil.developDesc = rows[i]["developDesc"].ToString();
                cantavil.cantavilCreator = rows[i]["cantavilCreator"].ToString();
                cantavil.cantavilCreateDate = Convert.ToDateTime(rows[i]["cantavilCreateDate"]);

                list.Add(cantavil);
            }

            return list ;
        }

        public ArrayCollection getAllCantavils(int pageIndex, int itemCount)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "SELECT * FROM Type where typeLevel = 2 and parentID in (select typeID from Type where typeLevel = 1 and tag = 'cityType')";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection list = new ArrayCollection();
            for(int i = 0; i < rows.Count; i++)
            {
                 Type newtype = new Type();
                 newtype.typeName = rows[i]["typeName"].ToString();
            newtype.typeLevel = Convert.ToInt16(rows[i]["typeLevel"]);
            newtype.typeID = Convert.ToInt16(rows[i]["typeID"]);
            newtype.typeCreator = rows[i]["typeCreator"].ToString();
            //newtype.typeCreateDate = Convert.ToDateTime(rows[i]["typeCreateDate"]);
            newtype.parentID = Convert.ToInt16(rows[i]["typeFlag"]);
            newtype.children = _getChildren(newtype.typeID);

            NewCantavil cantavil = new NewCantavil();
            cantavil.type = newtype;
            cantavil.cantavils = _getCantavilByFirstID(newtype.typeID, pageIndex, itemCount);
            list.Add(cantavil);
            }
            return list;
        }

        public ArrayCollection _getCantavilByFirstID(Int64 firstCityID, int pageIndex, int itemCount)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select distinct  Cantavil.* from Type, Cantavil where cantavil.typeID in" +
                        "(select typeID from Type where type.parentID = " + firstCityID.ToString() + ")";
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            ArrayCollection list = new ArrayCollection();
            int i;
            for (i = 0; i < rows.Count; i++)
            {
                Cantavil cantavil = new Cantavil();
                cantavil.cantavilID = Convert.ToInt64(rows[i]["cantavilID"]);
                cantavil.typeID = Convert.ToInt64(rows[i]["typeID"]);
                cantavil.pictureID = Convert.ToInt64(rows[i]["pictureID"]);
                cantavil.cantavilName = rows[i]["cantavilName"].ToString();
                cantavil.cantavilType = rows[i]["cantavilType"].ToString();
                cantavil.cantavilAddress = rows[i]["cantavilAddress"].ToString();
                cantavil.cantavilRemark = rows[i]["cantavilRemark"].ToString();
                cantavil.cantavilPic = rows[i]["cantavilPic"].ToString();
                cantavil.cantavilDeveloper = rows[i]["cantavilDeveloper"].ToString();
                cantavil.developDesc = rows[i]["developDesc"].ToString();
                cantavil.cantavilCreator = rows[i]["cantavilCreator"].ToString();
                cantavil.cantavilCreateDate = Convert.ToDateTime(rows[i]["cantavilCreateDate"]);

                list.Add(cantavil);
            }

            return list;
        }
      
        public List<Brand> deleteABrandByID(Brand brand)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            Boolean isDelete = _checkGoods(brand.brandID);

            ArrayCollection list = new ArrayCollection();
            if (isDelete)
            {
                using (SqlConnection connection = new SqlConnection(sqlhelper.ConnectionString))
                {
                    string select = "select * from Brand where brandID =" + brand.brandID.ToString();
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(select, connection);
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    connection.Open();
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, "Brand");
                    DataTable dt = dataSet.Tables[0];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    { dt.Rows[i].Delete(); }
                    adapter.Update(dataSet, "Brand");
                }
                return housediy.getAllBrands();
            }
            else
            {
                return housediy.getAllBrands();
            }
        }

        public Boolean _checkGoods(Int64 brandID)
        {
            HouseDiyHelper sqlhelper = HouseDiyHelper.GetInstance();
            string sql = "select * from Goods where brandID = " + brandID.ToString();
            DataSet ds = sqlhelper.FillDataSet(sql, null);
            DataRowCollection rows = ds.Tables[0].Rows;

            if (rows.Count > 0)
                return false;
            else
                return true;
        }

    }
}
